﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 系统设置选项表操作类(初步审核通过)
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class OptionsDAO
    {
        #region 添加选项
        /// <summary>
        /// 向选项设置表中插入一行数据,返回受影响行数
        /// </summary>
        /// <param name="option">要插入的对象</param>
        /// <returns>受影响行数</returns>
        public static int Insert(Option option)
        {
            string sql = @"INSERT INTO [Blog_Options]
                                    (
                                        [option_name],
                                        [option_value],
                                        [option_isenable],
                                        [option_desc]
                                    )
                                    VALUES
                                    (
                                        @Name,
                                        @Value,
                                        @IsEnable,
                                        @Description
                                    );";
            return ExecuteNonQuery(option, sql);
        }

        #endregion

        #region 删除选项
        /// <summary>
        /// 根据id删除对应的选项记录
        /// </summary>
        /// <param name="optionid">要删除记录的ID</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int optionid)
        {
            string sql = "DELETE FROM [Blog_Options] WHERE (option_id=@Id)";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", optionid));
        }
        #endregion

        #region 更新选项

        #region UpdateValueByName
        /// <summary>
        /// 根据选项名称更新对应的选项值
        /// </summary>
        /// <param name="name">要修改的选项名称</param>
        /// <param name="value">要更新的值</param>
        /// <returns>返回受影响行数</returns>
        public static int UpdateValueByName(string name, string value)
        {
            string sql = "UPDATE [Blog_Options] SET [option_value]=@Value WHERE [option_name]=@Name";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Value", value), new SqlParameter("@Name", name));
        }
        #endregion

        #region Update Option
        /// <summary>
        /// 更新一个选项对象
        /// </summary>
        /// <param name="option">要更新的对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(Option option)
        {
            string sql = @"UPDATE [Blog_Options] 
                                   SET 
                                        [option_name]=@Name,
                                        [option_value]=@Value,
                                        [option_isenable]=@IsEnable,
                                        [option_desc]=@Description
                                    WHERE 
                                        [option_id]=@Id;";
            return ExecuteNonQuery(option, sql);
        }
        #endregion

        #endregion

        #region 获取选项

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("option_id", "Blog_Options");
        }
        #endregion

        #region 选取所有选项信息
        /// <summary>
        /// 取出所有选项设置信息
        /// </summary>
        /// <returns>包涵所有设置信息的数据集合</returns>
        public static List<Option> SelectOptions()
        {
            string sql = @"SELECT 
                                        [option_id],
                                        [option_name], 
                                        [option_value],
                                        [option_isenable], 
                                        [option_desc]
                                    FROM 
                                        [Blog_Options]";
            return Select(sql);
        }
        #endregion

        #region 根据选项名称选取选项值
        /// <summary>
        /// 通过数据表中的选项名称获取对应设置的值
        /// </summary>
        /// <param name="name">选项名称</param>
        /// <returns>对应的选项值</returns>
        public static object SelectValue(string name)
        {
            string sql = "SELECT [option_value] FROM [Blog_Options] WHERE ([option_name]=@Name)";
            return SqlHelper.ExecuteScalar(sql, new SqlParameter("@Name", name));
        }
        #endregion

        #region 根据选项ID选取所有选项对象
        /// <summary>
        /// 根据选项ID选取所有选项对象
        /// </summary>
        /// <returns>指定ID的选项对象</returns>
        public static Option SelectOption(int optionid)
        {
            string sql = @"SELECT 
                                        [option_id],
                                        [option_name], 
                                        [option_value],
                                        [option_isenable], 
                                        [option_desc]
                                    FROM 
                                        [Blog_Options]
                                    WHERE
                                        [option_id]=@Id;";
            return Select(sql, new SqlParameter("@Id", optionid))[0];
        }

        #endregion

        #endregion

        #region 本类私有方法

        #region select
        /// <summary>
        /// 私有选择方法
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns>执行结果集合</returns>
        private static List<Option> Select(string sql, params SqlParameter[] parameter)
        {
            List<Option> list = new List<Option>();
            SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter);
            while (reader.Read())
            {
                Option option = new Option(
                    reader.GetInt32(0),
                    reader.GetString(1),
                    reader.GetString(2),
                    reader.GetBoolean(3),
                    //reader.GetString(4));
                    //空值处理
                    reader.IsDBNull(4) ? null : reader.GetString(4));
                list.Add(option);
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行写入数据库操作
        /// </summary>
        /// <param name="option">写入数据库的对象</param>
        /// <param name="sql">数据库写入语句</param>
        /// <returns>返回受影响行数</returns>
        private static int ExecuteNonQuery(Option option, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("Name", option.Name),
                new SqlParameter("Value", option.Value),
                new SqlParameter("IsEnable", option.IsEnable),
                new SqlParameter("Description", string.IsNullOrEmpty(option.Description) ? DBNull.Value : (object)option.Description),
                new SqlParameter("@Id", option.Id));
        } 
        #endregion

        #endregion
    }
}
